import yaml
import urllib
import urllib2
import sqlite3
import csv
import xlrd
import datetime
import calendar
import httplib
import urlparse
import re
import threading
from threading import Thread
from time import gmtime,strftime,time,sleep,ctime,clock
from BeautifulSoup import BeautifulSoup, SoupStrainer
from Queue import Queue

class ShareRobot:
    
    config = yaml.load(open('config.yaml'))
    conn = sqlite3.connect(config['development']['database_share'])
    cursor = conn.cursor()
    table = {
        'symbols':'symbols',
        'schema':'schema_migrations',
        'urls':'urls',
        'proxys':'proxys',
        'trades':'trades'        
        }
    today = datetime.date.today().strftime("%Y%m%d")
    start_time = 20000101
    end_time = today    
    urls = Queue()
    trades = Queue()
    crawled_urls = []
    failed_urls = []
    failed_max = 1
    crawled_num  = 0
    counter = 0
    delay = 0.1
    run_start = clock()
    run_finish = clock()
        
    def __init__(self, delay=0.1, retry = 3):
        self.run_start = clock()
        self.setStartTime()
        self.setEndTime()
        print "************* Update Share Database At " + str(self.today) + " *************"
        print "************* From " + str(self.start_time) + " To " + str(self.end_time) + " *************"
        self.delay = delay
        self.failed_max = retry
        self.initDatabase()
   
    def initDatabase(self):
        try:
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                version varchar(16) UNIQUE,
                created varchar(14)
                )
                """
                %(self.table['schema'])
                )
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25) UNIQUE,
                name varchar(30),            
                exchange varchar(10)
                )
                """
                %(self.table['symbols'])
                )
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(            
                url varchar(255) UNIQUE,
                symbol varchar (25),
                visited boolean
                )
                """
                %(self.table['urls'])
                )
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                address varchar(25) UNIQUE,
                type varchar(10)
                )
                """
                %(self.table['proxys'])
                )
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                date datetime,
                opening float,
                closeing float,            
                low float,
                high float,
                volume int
                )          
                """
                %(self.table['trades'])
                )
            """
            Commit
            """
            self.conn.commit()
            self.setStartTime()
            self.setEndTime()
            """
            Save wanted urls
            """
            # self.clearOldUrls()
            self.saveWantedUrls(self.table['symbols'])
            
            print "************* Init Database Finished *************"
            
        except Exception,e:
            print e
            
    def getLatestDbVersion(self,table):
        try:
            if 0==self.getRecNum(table):
                version = False
            else:
                self.cursor.execute(
                """
                SELECT version from %s ORDER BY created DESC
                """
                %(table)
                )
                version = int(self.cursor.fetchone()[0])
        except Exception,e:
            print e
        return version        
        
    def updateDbVersion(self):
        try:            
            if self.today == self.getLatestDbVersion(self.table['schema']):
                updated = True
            else:
                self.cursor.execute(     
                    """
                    INSERT INTO schema_migrations (version,created) VALUES (%s,%s)
                    """
                    %(self.today,strftime("%Y%m%d%H%M%S",gmtime(time()+28800)))
                    )
                self.conn.commit()
                updated = True
        except Exception,e:
            updated = False
            print e
        return updated
        
    def getRecNum(self,table):
        try:
            self.cursor.execute(            
            """
            SELECT count(*) FROM %s
            """
            %(table)
            )
            return self.cursor.fetchone()[0]
        except Exception,e:
            print e
        
    def updateAllSymbols(self):        
        try:
            urls = []
            num_of_per_page = 50
            if 0 == self.getRecNum(self.table['symbols']):       
                for exchange in self.config['exchange']:
                    if 'ss' == exchange:
                        root_url = self.config['exchange']['ss']
                        for tab in range(1,3):
                            if 1 == tab:
                                num_of_stocks = 894
                            else:
                                continue                                
                            max = int(num_of_stocks/num_of_per_page) + 1   
                            for cursor in range(0,max):
                                urls.append(root_url + "&CURSOR=" + str(cursor*50+1)+"&tab_flg="+str(tab))
                        for url in urls:
                            self.getSymbolsFromSS(exchange,url)
                        pass
                    elif 'sz' == exchange:
                        url = self.config['exchange']['sz']
                        self.getSymbolsFromSZ(exchange,url)
                    else:
                        continue
            else:
                #TODO
                pass
            self.conn.commit()
            print "************* All symbols have been updated *************"
        except Exception,e:
            print e                   
            
    def getSymbolsFromSS(self,exchange,url):
        try:            
            url_is_exists = self.httpExists(url)
            if url_is_exists:
                content = ""
                symbol = ""
                name = ""
                symbols = []
                page = urllib2.urlopen(url).read()
                soup = BeautifulSoup(page)
                tdTag = soup.findAll("td", "table3" )
                for index in range(0,len(tdTag)):
                    if index < 5:
                        continue
                    else:
                        content = str(tdTag[index].contents[0])
                        if 0 != index%2:
                            symbol = content[-10:-4] + "." + exchange
                        elif 0 == index%2:
                            name = content                            
                        else:
                            pass                        
                    if 0 == index%2:
                        symbols = [symbol,name.decode('utf-8'),exchange]
                        self.cursor.execute(  
                        """
                        INSERT INTO %s(symbol,name,exchange) VALUES (?,?,?)
                        """
                        %(self.table['symbols'])
                        ,symbols                
                        )
                    else:
                        pass
            else:
                return False
        except Exception,e:
            print e
        return True
    
    def getSymbolsFromSZ(self,exchange,url):
        try:
            url_is_exists = self.httpExists(url)
            if url_is_exists:
                content = ""
                symbol = ""
                name = ""
                symbols = []
                page = urllib2.urlopen(url).read()
                soup = BeautifulSoup(page)
                trTag = soup.findAll("tr", "cls-data-tr" )
                for index in range(0,len(trTag)):                    
                    symbol = str(trTag[index].contents[5].contents[0]) + "." + exchange
                    name = str(trTag[index].contents[6].contents[0])                    
                    if None != symbol and None != name:
                        symbols = [symbol,name.decode('utf-8'),exchange]
                        self.cursor.execute(  
                        """
                        INSERT INTO %s(symbol,name,exchange) VALUES (?,?,?)
                        """
                        %(self.table['symbols'])
                        ,symbols                
                        )
                    else:
                        continue
                
            else:
                return False
        except Exception,e:
            print e
        return True
    
        
    def httpExists(self,url):
        host,path = urlparse.urlsplit(url)[1:3]
        if ':' in host:
            host,port =host.split(':',1)
            try:
                port = int(port)
            except ValueError:
                print 'Invalid port number %r' % (port,)
                return False
        else:
            port = None
            
        try:
            connection = httplib.HTTPConnection(host,port=port)
            connection.request("HEAD",path)
            resp = connection.getresponse()
            if resp.status == 200 or 301 or 400:
                found = True
            elif resp.status == 302 or 404:
                found = self.__httpExists__(urlparse.urljoin(url,resp.getheader('location','')))
            else:
                print "Status %d %s: %s" % (resp.status,resp.reason,url)
                found = False
        except Exception,e:
            print e.__class__,e,url
            found = False
        return found
    
    def getLastFriday(self):
        try:
            lastFriday = datetime.date.today()
            oneday = datetime.timedelta(days=1)
            while lastFriday.weekday()!= calendar.FRIDAY:
                lastFriday -= oneday
            return lastFriday.strftime("%Y%m%d")
        except Exception,e:
            print e
            
            
    def getLastMonday(self):
        try:
            lastMonday = datetime.date.today()
            oneday = datetime.timedelta(days=1)
            while lastMonday.weekday()!= calendar.MONDAY:                
                lastMonday -= oneday
            return lastMonday.strftime("%Y%m%d")
        except Exception,e:
            print e
            
    def setEndTime(self):
        lastFriday = int(self.getLastFriday())
        today = int(self.today)
        if today > lastFriday:
            if abs(today-lastFriday)<=2:
                self.end_time = lastFriday
            else:
                self.end_time = today
        else:
            self.end_time = lastFriday
            
    def setStartTime(self):
        if 0!= self.getRecNum(self.table['schema']):
            self.start_time = int(self.getLastMonday())
        else:
            self.start_time = 20000101
            
                
    
    def getYahooFinaceAPI(self,start_time,end_time,symbol='IBM'):
        if start_time - end_time < 0:            
            """
            Start time
            """
            start_month = str(start_time)[4:6]
            start_day = str(start_time)[6:8]
            start_year = str(start_time)[0:4]
            
            """
            End time
            """
            end_month = str(end_time)[4:6]
            end_day = str(end_time)[6:8]
            end_year = str(end_time)[0:4]
            
            """
            Get yahoo finace api
            """
            yahoo_finace_api = "http://ichart.yahoo.com/table.csv?s=" + symbol + "&a=" + start_month + "&b=" + start_day +"&c=" + start_year + "&d="+ end_month +"&e="+ end_day +"&f="+ end_year +"&g=d&ignore=.csv"
        else:
            yahoo_finace_api = False 
            
        return yahoo_finace_api   
            
    def saveWantedUrls(self,symbols):
        try:
            while 0 == self.getRecNum(symbols):
                self.updateAllSymbols()
            self.cursor.execute(            
            """
            SELECT * FROM %s
            """
            %(symbols)
            )
            wanted = self.cursor.fetchall()            
            for row in wanted:
                yahoo_finace_api = self.getYahooFinaceAPI(self.start_time,self.end_time,row[0])
                if False != yahoo_finace_api:
                    self.cursor.execute(  
                    """
                    INSERT INTO urls(symbol,url,visited) VALUES (?,?,?)
                    """            
                    ,(row[0],yahoo_finace_api,'0')
                    )                                 
                else:
                    pass
            self.conn.commit()
            if 0 == self.getNewUrlsNum():
                print "************* No new urls need be crawled *************"
            else:                
                print "************* New urls have been crawled *************"          
            
        except Exception,e:
            print e            
    
    def getWantedUrls(self):
        try:
            self.cursor.execute(  
            """
            SELECT * FROM %s WHERE visited = '0'
            """            
            %(self.table['urls'])
            )
            wantedUrls =  self.cursor.fetchall()
            for row in wantedUrls:
                self.urls.put(row)
            print "************* " + str(self.urls.qsize()) + " urls will be crawled *************"
        except Exception,e:
            print e
    
    def delCrawledUrls(self):
        try:
            for url in self.crawled_urls:
                self.cursor.execute(  
                """
                UPDATE %s SET visited = '1' WHERE url = '%s'
                """            
                %(self.table['urls'],url)
                )
            self.urls = Queue()
            self.conn.commit()
        except Exception,e:
            print e
            
    
    def getFinaceHistory(self):
        args = self.urls.get()
        symbol = args[1]
        url = args[0]
        url_is_exists = self.httpExists(url)        
        if url_is_exists:
            request = urllib2.Request(url)
            request.add_header('User-Agent', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.1.14) Gecko/20080404 (FoxPlus) Firefox/2.0.0.14')
            try:
                yahoo_csv = urllib2.urlopen(request,timeout=30)
                sleep(self.delay)
                history = csv.reader(yahoo_csv)
                for row in history:
                    if ['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'] != row:
                        day_trade = [symbol,row[0],row[1],row[2],row[3],row[4],row[5]]
                        self.trades.put(day_trade)
                    else:
                        pass
                self.crawled_urls.append(url)
                self.crawled_num +=1
                print "Processing trades: " + str(self.trades.qsize())
                print "Information of " + symbol + " has been crawled."
                
            except Exception,e:
               self.failed_urls.append(url)               
               if self.failed_urls.count(url) >= self.failed_max:                    
                    self.crawled_urls.append(url)                    
               else:
                    pass
               print "Information of "  + symbol + " can not been got."
               print e  
             
            else:
                pass
        
        
    def saveDayTrade(self,day_trade):
        try:
            self.cursor.execute(     
            """
            INSERT INTO %s(symbol,date,opening,high,low,closeing,volume) VALUES (?,?,?,?,?,?,?)
            """
            %(self.table['trades'])
            ,day_trade
            )
        except Exception,e:
            print e       
        
    
    def crawl(self,threads = 100):
        """
        Process stock infomation
        """
        try:
            if self.getNewUrlsNum() < threads:
                threads = self.getNewUrlsNum()
            else:
                pass
            pool = []            
            for index in xrange(0, threads):                
                spider = threading.Thread(target=self.getFinaceHistory)
                spider.daemon = True
                pool.append(spider)
                
            for spider in pool:    
                spider.start()
                 
            for spider in pool:
                spider.join()          
            
        except Exception,e:
               print e
               
    def save(self,max = 100000):
        try:            
            while True:    
                self.saveDayTrade(self.trades.get())                          
                self.counter +=1                
                if  0 == self.counter%max:                
                    self.conn.commit()
                    print "************* Saving *************"
                    print "************* "+ str(self.counter) +"  records have been saved *************"                    
                else:
                    pass
                if 0 == self.trades.qsize():
                    self.conn.commit()
                    break
                else:
                    pass
        except Exception,e:
               print e
               
    def getNewUrlsNum(self):
        try:
            self.cursor.execute(  
            """
            SELECT COUNT(*) FROM %s WHERE visited = '0'
            """            
            %(self.table['urls'])
            )
            return self.cursor.fetchone()[0]
        except Exception,e:
               print e
               
    def clearOldUrls(self):
        try:
            if 0!= self.getRecNum(self.table['urls']):
                self.cursor.execute(
                """
                DELETE FROM %s WHERE visited = '0'                         
                """
                %(self.table['urls'])
                )
                self.conn.commit()
                cleared = True
            else:
                cleared = False
            return cleared
        except Exception,e:
           print e
        
        
        
    def run(self,threads = 10):
        try:
            while 0 != self.getNewUrlsNum():
                self.getWantedUrls()                
                self.crawl(threads)
                self.delCrawledUrls()
                self.save()
            if 0 == self.getNewUrlsNum():
                self.conn.commit()
                self.updateDbVersion()
                self.conn.close()
                self.run_finish = clock()
                print "************* Run Time: " + str(self.run_finish - self.run_start) + " *************"
                print "************* All Done! *************"
            else:
                pass
        except Exception,e:
            print e

